﻿using Oracle.ManagedDataAccess.Client;
using System.Data;
using System.Threading.Tasks;
using static AccountOperations.BankAccountGenerator;
using static AccountOperations.AccountOperations;
using static LoanCalculator.LoanCalculator;
using static System.Windows.Forms.VisualStyles.VisualStyleElement.TreeView;
using System.Numerics;
using System.Transactions;
using System.Data.Common;

namespace LoanOperations
{
    public static class LoanOperations
    {
        private const string connectionString = @"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=47.96.39.153)(PORT=1521))(CONNECT_DATA=(SID=orcl)));
                                                 User Id=system;
                                                 Password=Tongji123456;
                                                 Connection Timeout=5;";

        public static DataTable debug()
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                connection.Open();
                string query = @"
select * from DBA_SCHEDULER_JOBS
where JOB_NAME = 'PROCESS_REPAYMENTS_JOB'
";
                using (OracleCommand command = new OracleCommand(query, connection))
                {
                    using (OracleDataReader reader = command.ExecuteReader())
                    {
                        DataTable dataTable = new DataTable();
                        dataTable.Load(reader);
                        return dataTable;
                    }
                }
            }
        }

        private static void CreateScheduleProgram()
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                connection.Open();
                string query = @"
BEGIN
   DBMS_SCHEDULER.create_job (
      job_name        => 'PROCESS_REPAYMENTS_JOB',
      job_type        => 'PLSQL_BLOCK',
      job_action      => 'BEGIN DEDUCTION; END;',
      start_date      => TO_TIMESTAMP('2024-09-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS'),
      repeat_interval => 'FREQ=MONTHLY; BYMONTHDAY=1; BYHOUR=12; BYMINUTE=0; BYSECOND=0',
      enabled         => TRUE
   );
END;";
                using (OracleCommand command = new OracleCommand(query, connection))
                {
                    command.ExecuteNonQuery();
                }
            }
        }

        private static void DeleteScheduleProgram()
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                connection.Open();
                string query = @"
BEGIN
   DBMS_SCHEDULER.drop_job(
      job_name => 'PROCESS_REPAYMENTS_JOB'
   );
END;";
                using (OracleCommand command = new OracleCommand(query, connection))
                {
                    command.ExecuteNonQuery();
                }
            }
        }

        public static bool LoanToEnd(string loanID)
        {
            try
            {
                using (OracleConnection connection = new(connectionString))
                {
                    // 打开数据库连接
                    connection.Open();

                    // 构建更新语句
                    string updateQuery = @"UPDATE LOAN SET STATE = :state, 
                        REPAYMENT_BALANCE = :balance, 
                        END_TIME = :endTime 
                        WHERE LOAN_ID = :loanID";
                    using (OracleCommand command = new OracleCommand(updateQuery, connection))
                    {
                        command.Parameters.Add(":state", OracleDbType.Varchar2).Value = "已结束";
                        command.Parameters.Add(":balance", OracleDbType.Decimal).Value = 0;
                        DateTime now = DateTime.Now;
                        command.Parameters.Add(":endTime", OracleDbType.Date).Value = now;
                        command.Parameters.Add(":loanID", OracleDbType.Varchar2).Value = loanID;

                        // 执行更新操作
                        if (command.ExecuteNonQuery() > 0)
                        {
                            return true;
                        }
                        else
                        {
                            return false;
                        }
                    }
                }
            }
            catch (Exception)
            {
                MessageBox.Show("数据库连接失败，请检查网络连接！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return false;
            }
        }

        public static string LoanProcessing(string cardID, string branchCardID, decimal amount, int year, RepaymentType type)
        {
            string loanID = LoanIDGenerator.GenerateUniqueLoanID();
            if (LoanProcessing(loanID, cardID, branchCardID, amount, year, type))
            {
                return loanID;
            }
            else
            {
                return "";
            }
        }
        
        // 贷款办理
        private static bool LoanProcessing(string loanID, string cardID, string branchCardID, decimal amount, int year, RepaymentType type)
        {
            decimal sum = GetSumPrincipleInterest(amount, year, type);
            // 创建连接
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                try
                {
                    decimal interestRate = GetInterestRate(amount, year);
                    connection.Open();

                    // 插入贷款
                    string insertQuery = "INSERT INTO LOAN (LOAN_ID, CREDIT_CARD_NUMBERS, AMOUNT, REPAYMENT_BALANCE, INTEREST_RATE, BEGIN_TIME, END_TIME, STATE, IS_EQUAL_PRINCIPLE) " +
                                 "VALUES (:loanId, :cardID, :amount, :repaymentBalance, :interestRate, :beginTime, :endTime, :state, :isPrinciple)";

                    // 创建命令对象
                    using (OracleCommand command = new OracleCommand(insertQuery, connection))
                    {
                        // 设置参数
                        command.Parameters.Add(new OracleParameter("loanId", OracleDbType.Varchar2, 20)).Value = loanID;
                        command.Parameters.Add(new OracleParameter("creditCardNumber", OracleDbType.Varchar2, 20)).Value = cardID;
                        command.Parameters.Add(new OracleParameter("amount", OracleDbType.Decimal)).Value = amount;  // 贷款金额
                        command.Parameters.Add(new OracleParameter("repaymentBalance", OracleDbType.Decimal)).Value = sum;  // 还款剩余金额
                        command.Parameters.Add(new OracleParameter("interestRate", OracleDbType.Decimal)).Value = interestRate;  // 利率
                        DateTime now = DateTime.Now;
                        DateTime nextMonth = new DateTime(now.Year, now.Month, 1).AddMonths(year * 12);
                        DateTime adjustedDate = new DateTime(nextMonth.Year, nextMonth.Month, 1, 12, 0, 0);
                        command.Parameters.Add(new OracleParameter("beginTime", OracleDbType.Date)).Value = now;  // 开始时间
                        command.Parameters.Add(new OracleParameter("endTime", OracleDbType.Date)).Value = adjustedDate;  // 结束时间
                        command.Parameters.Add(new OracleParameter("state", OracleDbType.Varchar2, 20)).Value = "还款中";  // 状态
                        if (type == RepaymentType.EqualPrincipal)
                        {
                            command.Parameters.Add(new OracleParameter("isPrinciple", OracleDbType.Varchar2, 1)).Value = 1;
                        }
                        else
                        {
                            command.Parameters.Add(new OracleParameter("isPrinciple", OracleDbType.Varchar2, 1)).Value = 0;
                        }

                        // 执行插入操作
                        if (command.ExecuteNonQuery() > 0 && CreateLoanRepayment(loanID, cardID, branchCardID, amount, year, type))
                        {
                            return true;
                        }
                        else
                        {
                            return false;
                        }
                    }
                }
                catch (Exception)
                {
                    MessageBox.Show("数据库连接失败，请检查网络连接！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return false;
                }
            }
        }

        // 计算对应的剩余还款总和
        private static decimal GetSumPrincipleInterest(decimal amount, int year, RepaymentType type)
        {
            decimal[] repayments = CalculateMonthlyRepayment(amount, year, type);
            decimal sum = 0;
            for (int i = 0; i < repayments.Length; i++)
            {
                sum += repayments[i];
            }
            return sum;
        }

        // 查找当前利率
        public static decimal GetInterestRate(decimal amount, int year)
        {
            try
            {
                using (OracleConnection connection = new OracleConnection(connectionString))
                {
                    connection.Open();

                    string query = @"
                SELECT INTEREST_RATE 
                FROM LOAN_INTEREST_RATE 
                WHERE :principal BETWEEN LOAN_AMOUNT_MIN AND LOAN_AMOUNT_MAX
                  AND :years BETWEEN LOAN_YEARS_MIN AND LOAN_YEARS_MAX";

                    using (OracleCommand command = new OracleCommand(query, connection))
                    {
                        command.Parameters.Add(new OracleParameter("principal", amount));
                        command.Parameters.Add(new OracleParameter("years", year));

                        object result = command.ExecuteScalar();
                        if (result != null)
                        {
                            decimal interestRate = Convert.ToDecimal(result);
                            return interestRate;
                        }
                        else
                        {
                            return new decimal(0);
                        }
                    }
                }
            }
            catch (Exception)
            {
                MessageBox.Show("数据库连接失败，请检查网络连接！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return new decimal(0);
            }
        }

        private static string GenerateOperateId()
        {
            return Guid.NewGuid().ToString("N").ToUpper();
        }

        // 创建每条贷款对应的多条还款任务
        private static bool CreateLoanRepayment(string loanID, string cardID, string branchCardID, decimal principal, int year, RepaymentType type)
        {
            decimal[] repayments = CalculateMonthlyRepayment(principal, year, type);
            for (int i = 0; i < repayments.Length; i++)
            {
                string taskID = GenerateOperateId();
                DateTime now = DateTime.Now;
                DateTime firstDayOfNextMonth = new DateTime(now.Year, now.Month, 1).AddMonths(i + 1);
                DateTime adjustedDate = new DateTime(firstDayOfNextMonth.Year, firstDayOfNextMonth.Month, 1, 12, 0, 0);
                if (!CreateSingleRepayment(taskID, repayments[i], adjustedDate, loanID, cardID, branchCardID))
                {
                    return false;
                }
            }
            return true;
        }

        // 创建每条贷款对应的单条还款任务
        public static bool CreateSingleRepayment(string taskID, decimal repayment, DateTime adjustedDate, string loanID, string cardID, string branchCardID)
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    // 插入还款任务
                    string insertQuery = "INSERT INTO REPAYMENT_TASKS (TASK_ID, CARD_ID, AMOUNT, DUE_DATE, LOAN_ID, BRANCH_CARD) " +
                        "VALUES (:taskID, :cardID, :amount, :adjustedDate, :loanID, :branchCardID)";

                    // 创建命令对象
                    using (OracleCommand command = new OracleCommand(insertQuery, connection))
                    {
                        // 设置参数
                        command.Parameters.Add(new OracleParameter("taskID", OracleDbType.Varchar2, 32)).Value = taskID;
                        command.Parameters.Add(new OracleParameter("cardID", OracleDbType.Varchar2, 20)).Value = cardID;
                        decimal value = Math.Round(repayment, 2);
                        command.Parameters.Add(new OracleParameter("amount", OracleDbType.Decimal)).Value = value;  // 每月还款金额
                        command.Parameters.Add(new OracleParameter("adjustedDate", OracleDbType.Date)).Value = adjustedDate;
                        command.Parameters.Add(new OracleParameter("loanId", OracleDbType.Varchar2, 20)).Value = loanID;
                        command.Parameters.Add(new OracleParameter("branchCardID", OracleDbType.Varchar2, 20)).Value = branchCardID;

                        // 执行插入操作
                        if (command.ExecuteNonQuery() > 0)
                        {
                            return true;
                        }
                        else
                        {
                            return false;
                        }
                    }
                }
                catch (Exception)
                {
                    MessageBox.Show("数据库连接失败，请检查网络连接！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return false;
                }
            }
        }

        // 删除每条贷款对应的多条还款任务
        public static bool DeleteRepayment(string loanID)
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    // 删除还款任务
                    string insertQuery = @"
DELETE FROM REPAYMENT_TASKS 
WHERE LOAN_ID = :loanID";

                    // 创建命令对象
                    using (OracleCommand command = new OracleCommand(insertQuery, connection))
                    {
                        // 设置参数
                        command.Parameters.Add(new OracleParameter("loanID", OracleDbType.Varchar2, 20)).Value = loanID;

                        // 执行插入操作
                        if (command.ExecuteNonQuery() > 0)
                        {
                            return true;
                        }
                        else
                        {
                            return false;
                        }
                    }
                }
                catch (Exception)
                {
                    MessageBox.Show("数据库连接失败，请检查网络连接！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return false;
                }
            }
        }

        // 使用银行卡号进行贷款查询
        public static DataTable GetLoansByCardID(string cardID)
        {
            try
            {
                using (OracleConnection connection = new OracleConnection(connectionString))
                {
                    connection.Open();

                    string query = "SELECT * FROM LOAN WHERE CREDIT_CARD_NUMBERS = :cardID";

                    using (OracleCommand command = new OracleCommand(query, connection))
                    {
                        command.Parameters.Add(new OracleParameter("cardID", cardID));

                        using (OracleDataReader reader = command.ExecuteReader())
                        {
                            DataTable dataTable = new DataTable();
                            dataTable.Load(reader);
                            return dataTable;
                        }
                    }
                }
            }
            catch (Exception)
            {
                MessageBox.Show("数据库连接失败，请检查网络连接！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return new DataTable(); // 出错返回空表
            }
        }

        public static string GetLoanBalance(string loanID)
        {
            DataTable dataTable = GetLoansByLoanID(loanID);
            if (dataTable != null && dataTable.Rows.Count > 0)
            {
                DataRow row = dataTable.Rows[0];
                string balance = row["REPAYMENT_BALANCE"].ToString();
                return balance == null ? "" : balance;
            }
            else
            {
                return "";
            }
        }

        public static string GetLoanBackCard(string loanID)
        {
            DataTable dataTable = GetLoanRepayment(loanID);
            if (dataTable != null && dataTable.Rows.Count > 0)
            {
                DataRow row = dataTable.Rows[0];
                string branchCard = row["BRANCH_CARD"].ToString();
                return branchCard == null ? "" : branchCard;
            }
            else
            {
                return "";
            }
        }

        public static DataTable GetLoanRepayment(string loanID)
        {
            try
            {
                using (OracleConnection connection = new OracleConnection(connectionString))
                {
                    connection.Open();

                    string query = "SELECT * FROM REPAYMENT_TASKS WHERE LOAN_ID = :loanID";

                    using (OracleCommand command = new OracleCommand(query, connection))
                    {
                        command.Parameters.Add(new OracleParameter("loanID", loanID));

                        using (OracleDataReader reader = command.ExecuteReader())
                        {
                            DataTable dataTable = new DataTable();
                            dataTable.Load(reader);
                            return dataTable;
                        }
                    }
                }
            }
            catch (Exception)
            {
                MessageBox.Show("数据库连接失败，请检查网络连接！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return new DataTable(); // 出错返回空表
            }
        }

        public static decimal GetLoanAmount(string loanID)
        {
            DataTable dataTable = GetLoansByLoanID(loanID);
            if (dataTable != null && dataTable.Rows.Count > 0)
            {
                DataRow row = dataTable.Rows[0];
                decimal amount = Decimal.Parse(row["AMOUNT"].ToString());
                return amount;
            }
            else
            {
                return -1;
            }
        }

        // 使用贷款号进行贷款查询
        public static DataTable GetLoansByLoanID(string loanID)
        {
            DataTable dataTable = new DataTable();
            try
            {
                using (OracleConnection connection = new OracleConnection(connectionString))
                {
                    connection.Open();
                    // 数据库操作
                    string selectQuery = "SELECT * FROM LOAN WHERE LOAN_ID = :loanID";
                    using (OracleCommand command = new OracleCommand(selectQuery, connection))
                    {
                        OracleParameter param = new OracleParameter(":loanID", OracleDbType.Varchar2);
                        param.Value = loanID; // 替换成实际的银行卡号
                        command.Parameters.Add(param);

                        // 执行查询
                        using (OracleDataReader reader = command.ExecuteReader())
                        {
                            dataTable.Load(reader); // 将查询结果加载到 DataTable
                            return dataTable;
                        }
                    }
                }
            }
            catch (Exception)
            {
                MessageBox.Show("数据库连接失败，请检查网络连接！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return new DataTable(); // 出错返回空表
            }
        }

        public static string GetGenerateID()
        {
            string results = "";

            // 创建数据库连接对象
            try
            {
                using (OracleConnection connection = new(connectionString))
                {
                    // 打开数据库连接
                    connection.Open();
                    // 数据库操作
                    using (OracleCommand command = new OracleCommand("GENERATE_UNQUE_ID", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        OracleParameter returnValue = new OracleParameter("return_value", OracleDbType.Varchar2, 2000)
                        {
                            Direction = ParameterDirection.ReturnValue
                        };
                        command.Parameters.Add(returnValue);
                        command.ExecuteNonQuery();
                        if (returnValue.Value != DBNull.Value)
                        {
                            results = returnValue.Value.ToString();
                        }
                    }
                }
            }
            catch (Exception)
            {
                MessageBox.Show("数据库连接失败，请检查网络连接！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            return results;
        }

        public static string[] CreateLoanTransfer(string branchCard, string targetCard, decimal amount, string loanID)
        {
            string operateID1 = GetGenerateID();
            if (operateID1 == null || operateID1 == "")
            {
                return null;
            }
            if (!BankCardBalanceUpdate(branchCard, -amount) || !BankCardBalanceUpdate(targetCard, amount))
            {
                return null;
            }

            try
            {
                using (OracleConnection connection = new(connectionString))
                {
                    // 打开数据库连接
                    connection.Open();
                    using (OracleTransaction transaction = connection.BeginTransaction())
                    {

                        // 数据库操作
                        string transactionQuery1 = @"
INSERT INTO TRANSACTION_HISTORY (TRANSACTION_ID, TRANSACTION_CURRENCY_TYPE, TRANSACTION_TIME, AMOUNT, TRANSACTION_COMMENT, CREDIT_CARD_NUMBERS, ASSIST_CARD_NUMBERS, STATE)
VALUES (:transactionID, '人民币', SYSDATE, :amount, '贷款放款', :outCard, :inCard, '已完成')";
                        using (OracleCommand command = new OracleCommand(transactionQuery1, connection))
                        {
                            // 添加参数
                            command.Parameters.Add(new OracleParameter("transactionID", operateID1));
                            command.Parameters.Add(new OracleParameter("amount", amount));
                            command.Parameters.Add(new OracleParameter("outCard", branchCard));
                            command.Parameters.Add(new OracleParameter("inCard", targetCard));
                            // 执行插入操作
                            if (command.ExecuteNonQuery() <= 0)
                            {
                                transaction.Rollback();
                                return null;
                            }
                        }

                        string operateID2 = GetGenerateID();
                        if (operateID2 == null || operateID2 == "")
                        {
                            transaction.Rollback();
                            return null;
                        }
                        string transactionQuery2 = @"
INSERT INTO TRANSACTION_HISTORY (TRANSACTION_ID, TRANSACTION_CURRENCY_TYPE, TRANSACTION_TIME, AMOUNT, TRANSACTION_COMMENT, CREDIT_CARD_NUMBERS, ASSIST_CARD_NUMBERS, STATE)
VALUES (:transactionID, '人民币', SYSDATE, :amount, '转账', :outCard, :inCard, '已完成')";
                        using (OracleCommand command = new OracleCommand(transactionQuery2, connection))
                        {
                            // 添加参数
                            command.Parameters.Add(new OracleParameter("transactionID", operateID2));
                            command.Parameters.Add(new OracleParameter("amount", amount));
                            command.Parameters.Add(new OracleParameter("outCard", branchCard));
                            command.Parameters.Add(new OracleParameter("inCard", targetCard));
                            // 执行插入操作
                            if (command.ExecuteNonQuery() <= 0)
                            {
                                transaction.Rollback();
                                return null;
                            }
                        }

                        string operateID3 = GetGenerateID();
                        if (operateID3 == null || operateID2 == "")
                        {
                            transaction.Rollback();
                            return null;
                        }
                        string transactionQuery3 = @"
INSERT INTO TRANSACTION_HISTORY (TRANSACTION_ID, TRANSACTION_CURRENCY_TYPE, TRANSACTION_TIME, AMOUNT, TRANSACTION_COMMENT, CREDIT_CARD_NUMBERS, ASSIST_CARD_NUMBERS, STATE)
VALUES (:transactionID, '人民币', SYSDATE, :amount, '收账', :inCard, :outCard, '已完成')";
                        using (OracleCommand command = new OracleCommand(transactionQuery3, connection))
                        {
                            // 添加参数
                            command.Parameters.Add(new OracleParameter("transactionID", operateID3));
                            command.Parameters.Add(new OracleParameter("amount", amount));
                            command.Parameters.Add(new OracleParameter("inCard", targetCard));
                            command.Parameters.Add(new OracleParameter("outCard", branchCard));
                            // 执行插入操作
                            if (command.ExecuteNonQuery() <= 0)
                            {
                                transaction.Rollback();
                                return null;
                            }
                        }

                        string operateQuery = @"
INSERT INTO LOAN_OPERATE(TRANSACTION_ID, LOAN_ID) 
VALUES(:transactionID, :loanID)";
                        using (OracleCommand command = new OracleCommand(operateQuery, connection))
                        {
                            // 添加参数
                            command.Parameters.Add(new OracleParameter("transactionID", operateID1));
                            command.Parameters.Add(new OracleParameter("loanID", loanID));
                            // 执行插入操作
                            if (command.ExecuteNonQuery() <= 0)
                            {
                                transaction.Rollback();
                                return null;
                            }
                        }
                        transaction.Commit();
                        string[] values = {operateID2, operateID3 };
                        return values;
                    }
                }
            }
            catch (Exception)
            {
                MessageBox.Show("数据库连接失败，请检查网络连接！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return null;
            }
        }
    }

    public static class LoanIDGenerator
    {
        private static Random random = new Random();

        public static string GenerateUniqueLoanID()
        {
            string loanID;
            do
            {
                loanID = GenerateRandom20ID();
            } while (LoanOperations.GetLoansByLoanID(loanID).Rows.Count > 0);

            return loanID;
        }
    }

}
